from common import AlbumEntity,SearchFilter
import DatabaseConnection

class AlbumData(object):
    def __init__(self):
        pass

    def InsertAlbum(self,album):
        ID = None
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsAlbum',(album.ArtistID,album.Rotation,album.Genre,album.Description,\
	                            album.ReleaseDate,album.ReceiveDate,album.GoForAds,album.Promoter,album.AlbumName))
        ID = cursor.fetchone()[0]

        cursor.close()
        conn.commit()
        conn.close()

        return ID

    def UpdateAlbum(self,album):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdAlbum',(album.AlbumID,album.ArtistID,album.Rotation,album.Genre,album.Description,\
	                            album.ReleaseDate,album.ReceiveDate,album.GoForAds,album.Promoter,album.AlbumName))
        cursor.close()
        conn.commit()
        conn.close()

    def DeleteAlbum(self,ID):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelAlbum',(ID,))
        cursor.close()
        conn.commit()
        conn.close()

    #This method either return an object of type AlbumEntity or None (if there is no record with this ID)
    def SelectAlbumByID(self,ID):
        query = """ SELECT AlbumID,AlbumName,ArtistID,Rotation,Genre,Description,ReleaseDate,ReceiveDate,GoForAds,Promoter,ArtistName,RotationDesc,GenreDesc
                    FROM vwAlbum
                    WHERE AlbumID='%s'""" % ID

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return AlbumEntity.AlbumEntity(albumID=result[0][0],albumName=result[0][1],artistID=result[0][2],rotation=result[0][3],genre=result[0][4],\
                                           description=result[0][5],releaseDate=result[0][6],receiveDate=result[0][7],goForAds=result[0][8],\
                                           promoter=result[0][9],artistName=result[0][10],rotationDesc=result[0][11],genreDesc=result[0][12])
        else:
            return None

    #This method returns a list of objects of type AlbumEntity
    def SelectAlbumByFilter(self,searchFilter):
        query = """ SELECT AlbumID,AlbumName,ArtistID,Rotation,Genre,Description,ReleaseDate,ReceiveDate,GoForAds,Promoter,ArtistName,RotationDesc,GenreDesc
                    FROM vwAlbum"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(AlbumEntity.AlbumEntity(albumID=record[0],albumName=record[1],artistID=record[2],rotation=record[3],genre=record[4],
                                                     description=record[5],releaseDate=record[6],receiveDate=record[7],goForAds=record[8],\
                                                     promoter=record[9],artistName=record[10],rotationDesc=record[11],genreDesc=record[12]))
        return recordSet


    #This method returns a list of objects of type AlbumEntity
    def SelectAllAlbums(self):
        query = """ SELECT AlbumID,AlbumName,ArtistID,Rotation,Genre,Description,ReleaseDate,ReceiveDate,GoForAds,Promoter,ArtistName,RotationDesc,GenreDesc
                    FROM vwAlbum"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(AlbumEntity.AlbumEntity(albumID=record[0],albumName=record[1],artistID=record[2],rotation=record[3],genre=record[4],
                                                     description=record[5],releaseDate=record[6],receiveDate=record[7],goForAds=record[8],\
                                                     promoter=record[9],artistName=record[10],rotationDesc=record[11],genreDesc=record[12]))
        return recordSet


    def SelectAlbumsForReview(self):
        query = """ SELECT AlbumID,AlbumName,ArtistID,Rotation,Genre,Description,ReleaseDate,ReceiveDate,GoForAds,Promoter,ArtistName,RotationDesc,GenreDesc
                    FROM vwAlbum WHERE AlbumID NOT IN (SELECT AlbumID FROM Review)"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(AlbumEntity.AlbumEntity(albumID=record[0],albumName=record[1],artistID=record[2],rotation=record[3],genre=record[4],
                                                     description=record[5],releaseDate=record[6],receiveDate=record[7],goForAds=record[8],\
                                                     promoter=record[9],artistName=record[10],rotationDesc=record[11],genreDesc=record[12]))
        return recordSet
